Building Multi-Criteria Lookup Formulas

Course: Advanced Excel

Topic: Building Multi-Criteria Lookup Formulas

Course Link: Advanced Excel Course

Demo Link: Register for a Course Demo

Website: JustAcademy

1. Introduction to Multi-Criteria Lookup Formulas

Multi-criteria lookup formulas in Microsoft Excel are used to retrieve information by checking two or more conditions simultaneously. Unlike a basic lookup that searches using a single value, a multi-criteria lookup identifies a record that satisfies multiple requirements, such as employee name, department, location, product, category, or transaction date.

For example, a company may have multiple employees with the same name working in different departments. Searching only by employee name may return the wrong record. A multi-criteria formula can use the employee name and department together to identify the correct information.

Multi-criteria lookups are particularly useful in:

• Employee databases and payroll reports.
• Product pricing and inventory management.
• Sales and revenue analysis.
• Customer order tracking.
• Financial and accounting reports.
• Student records and examination results.
• Business dashboards and operational reporting.

2. Learning Objectives

• Understand the concept of multi-criteria lookups.
• Learn to combine INDEX and MATCH for multiple conditions.
• Build formulas using AND logic.
• Use XLOOKUP with multiple conditions in supported Excel versions.
• Understand how concatenated lookup keys work.
• Handle missing records using IFERROR.
• Identify common lookup errors and their solutions.
• Create dynamic lookup worksheets for practical business applications.

3. What Is a Multi-Criteria Lookup?

A multi-criteria lookup searches for a record using more than one condition. All specified conditions must match when the formula uses AND logic.

Consider the following employee sales dataset:

Suppose you want to retrieve the sales amount for Rahul in the Sales department in Pune. The formula must match all three conditions:

• Employee = Rahul
• Department = Sales
• City = Pune

Expected result: 42000

This is a multi-criteria lookup because the result depends on three conditions rather than just one.

4. Understanding the INDEX Function

The INDEX function returns a value from a specified range based on its relative position. In a multi-criteria lookup, INDEX typically returns the final result after MATCH identifies the row containing the required record.

Syntax:

=INDEX(array, row_num, [column_num])

For a single-column result range, such as D2:D6, INDEX can return the value from the required relative row:

=INDEX(D2:D6,3)

This returns the third value within D2:D6, which is the value stored in cell D4.

5. Understanding the MATCH Function

The MATCH function searches a row or column and returns the relative position of a matching value. When working with multiple criteria, logical tests can be combined to identify the row where all conditions are satisfied.

Syntax:

=MATCH(lookup_value, lookup_array, [match_type])

For an exact match, use 0 as the third argument:

=MATCH("Rahul",A2:A6,0)

This returns the position of the first Rahul in A2:A6. However, if the name appears more than once, this formula alone cannot distinguish between the records. Additional conditions are required.

6. Method 1: INDEX and MATCH with Multiple Criteria

One of the most flexible methods for building multi-criteria lookup formulas is combining INDEX with MATCH and multiplying logical tests.

General formula:

=INDEX(return_range,MATCH(1,(criteria_range1=criteria1)*(criteria_range2=criteria2),0))

Each comparison produces TRUE or FALSE values. In Excel arithmetic operations, TRUE behaves like 1 and FALSE behaves like 0. Multiplying the comparisons produces 1 only when both conditions are satisfied for the same row.

For three criteria, the formula becomes:

=INDEX(return_range,MATCH(1,(criteria_range1=criteria1)*(criteria_range2=criteria2)*(criteria_range3=criteria3),0))

This approach uses AND logic because every specified condition must be true for a row to qualify.

7. Practical Example: Looking Up Sales by Employee and Department

Assume the employee dataset is arranged in cells A1:D6:

To retrieve Rahul's sales in the Sales department, use:

=INDEX(D2:D6,MATCH(1,(A2:A6="Rahul")*(B2:B6="Sales"),0))

Result: 45000

The formula returns the first record matching Rahul and Sales, which is the Mumbai record. Since the same employee and department appear more than once, the formula returns the first matching row in the dataset.

To distinguish Rahul's Pune record, include the City condition as well:

=INDEX(D2:D6,MATCH(1,(A2:A6="Rahul")*(B2:B6="Sales")*(C2:C6="Pune"),0))

Result: 42000

8. Understanding the Formula Step by Step

Consider this formula:

=INDEX(D2:D6,MATCH(1,(A2:A6="Rahul")*(B2:B6="Sales")*(C2:C6="Pune"),0))

Step 1: Evaluate the Employee Condition

A2:A6="Rahul"

Excel compares every employee name with Rahul. Matching rows produce TRUE, while other rows produce FALSE.

Step 2: Evaluate the Department Condition

B2:B6="Sales"

Excel checks which rows belong to the Sales department.

Step 3: Evaluate the City Condition

C2:C6="Pune"

Excel identifies rows where the city is Pune.

Step 4: Combine the Conditions

The multiplication operator combines the logical results. Only the row matching Rahul, Sales, and Pune produces 1.

Step 5: Find the Matching Row

MATCH searches for 1 in the combined logical results and returns the relative position of the matching row.

Step 6: Return the Sales Amount

INDEX uses that position to retrieve the corresponding value from D2:D6, returning 42000.

9. Method 2: Using Cell References for Dynamic Criteria

Instead of typing conditions directly into the formula, store them in separate cells. This makes the worksheet reusable and easier to update.

Enter the following formula in F5:

=INDEX($D$2:$D$6,MATCH(1,($A$2:$A$6=F2)*($B$2:$B$6=F3)*($C$2:$C$6=F4),0))

When F2 contains Rahul, F3 contains Sales, and F4 contains Pune, the formula returns 42000.

Changing any selection updates the result automatically. The dollar signs keep the source ranges fixed when the formula is copied, while F2, F3, and F4 remain relative references.

10. Method 3: Handling Missing Results with IFERROR

If no record satisfies all the conditions, MATCH returns #N/A. IFERROR can display a clear message instead of an error.

Formula:

=IFERROR(INDEX($D$2:$D$6,MATCH(1,($A$2:$A$6=F2)*($B$2:$B$6=F3)*($C$2:$C$6=F4),0)),"No Matching Record")

If no employee record matches the selected criteria, Excel displays No Matching Record.

IFERROR can also hide errors unrelated to missing records. If the workbook requires detailed auditing, verify the source ranges and formula separately before applying error handling.

11. Method 4: Using XLOOKUP with Multiple Criteria

In Excel versions that support XLOOKUP and dynamic arrays, you can combine multiple logical conditions to create a lookup array. XLOOKUP searches for 1 in the combined conditions and returns the corresponding result.

Formula:

=XLOOKUP(1,(A2:A6=F2)*(B2:B6=F3)*(C2:C6=F4),D2:D6,"No Matching Record")

This formula checks employee, department, and city, then returns the matching sales amount. If no matching record exists, it displays No Matching Record.

Advantages of XLOOKUP:

• The formula includes a built-in argument for a not-found message.
• Separate lookup and return ranges make the formula easier to read.
• It can search in different directions and does not require a fixed column index.
• It supports flexible lookup designs in compatible Excel versions.

For workbooks used in older Excel versions, INDEX and MATCH may be a more compatible choice.

12. Method 5: Using Concatenated Lookup Keys

Another approach is to combine multiple fields into one lookup key. This can simplify some lookup formulas, especially when a helper column is practical.

For example, create a helper column E with the formula in E2:

=A2&"|"&B2&"|"&C2

Copy the formula down through the dataset. The resulting key combines employee, department, and city.

For Rahul in Sales in Pune, the combined key is:

Rahul|Sales|Pune

You can retrieve the corresponding sales amount using:

=XLOOKUP(F2&"|"&F3&"|"&F4,$E$2:$E$6,$D$2:$D$6,"No Matching Record")

This formula assumes that F2 contains the employee, F3 contains the department, and F4 contains the city.

Important considerations:

• Use a separator that will not normally appear in the source values.
• Ensure that the same concatenation pattern is used in both the helper column and the lookup formula.
• Be careful with values that can create ambiguous combined keys.
• A helper column is optional; logical-condition formulas can work without it.

13. Understanding AND and OR Logic

Multi-criteria formulas can represent different logical requirements. Choosing the correct logic is essential for accurate results.

AND Logic Example

To find sales for Rahul in Pune:

=INDEX(D2:D6,MATCH(1,(A2:A6="Rahul")*(C2:C6="Pune"),0))

Both conditions must match the same row.

OR Logic Example

To find the first sales record where the department is either Sales or Support, use a formula such as:

=XLOOKUP(1,((B2:B6="Sales")+(B2:B6="Support"))>0,D2:D6,"No Matching Record")

The addition operator combines the conditions, and the expression greater than zero identifies rows where at least one condition is true. This formula returns the first qualifying record, not every qualifying record.

For a list of all qualifying records, consider using the FILTER function in compatible Excel versions.

14. Using FILTER to Return Multiple Matching Records

INDEX and MATCH typically return a single matching record. If several rows satisfy the criteria and you need all of them, FILTER can be more appropriate in supported Excel versions.

For example, to return all employees in the Sales department:

=FILTER(A2:D6,B2:B6="Sales","No Matching Records")

To return all records where the department is Sales and the city is Mumbai:

=FILTER(A2:D6,(B2:B6="Sales")*(C2:C6="Mumbai"),"No Matching Records")

FILTER returns all matching rows instead of stopping at the first result. This is useful when analysing repeated transactions or records that share the same criteria.

15. Common Errors and Troubleshooting

16. Best Practices for Building Multi-Criteria Formulas

• Identify all required lookup conditions before writing the formula.
• Use exact matching for text labels, employee IDs, product codes, and similar identifiers.
• Ensure that all criteria ranges contain the same number of rows.
• Use absolute references for fixed datasets when formulas will be copied.
• Clean data to remove unwanted spaces and inconsistent values.
• Use unique identifiers where possible to avoid ambiguous results.
• Use IFERROR or a suitable not-found argument to improve user experience.
• Choose FILTER when all matching records are required rather than just the first one.
• Test formulas with valid, invalid, and duplicate combinations.
• Consider XLOOKUP when the workbook's Excel version supports it.

17. INDEX and MATCH vs. XLOOKUP vs. FILTER

18. Practical Exercises

Exercise 1: Employee Sales Lookup

Create an employee dataset with employee names, departments, cities, and sales amounts. Write a formula to return sales when the employee is Rahul, the department is Sales, and the city is Pune.

Expected formula pattern:

=INDEX(D2:D6,MATCH(1,(A2:A6="Rahul")*(B2:B6="Sales")*(C2:C6="Pune"),0))

Exercise 2: Product Price Lookup

Create a table containing product names, sizes, colours, and prices. Retrieve the price for a specific product, size, and colour.

Exercise 3: Student Results Lookup

Create a dataset containing student names, subjects, examination sessions, and marks. Retrieve the marks using all three criteria.

Exercise 4: Customer Order Lookup

Create a customer order dataset containing customer ID, order date, product code, and order amount. Retrieve the amount matching the required customer, date, and product.

Exercise 5: Error Handling

Modify a multi-criteria INDEX and MATCH formula so that it displays "No Matching Record" when no record satisfies the conditions.

Exercise 6: Return All Matches

Use FILTER to return every record where the department is Sales and the city is Mumbai.

19. Mini Project: Multi-Criteria Employee Reporting System

Project objective: Build an Excel worksheet that retrieves employee sales information using multiple conditions.

Project Requirements

• Create a dataset containing employee names, departments, cities, and sales amounts.
• Provide input cells for selecting an employee, department, and city.
• Use Data Validation dropdowns where appropriate.
• Write an INDEX and MATCH formula that checks all three criteria.
• Add error handling for missing combinations.
• Use consistent formatting for lookup inputs and results.
• Test the formula using duplicate names and different locations.
• Optionally create a FILTER-based section that displays all matching records.

Expected Outcome

The completed workbook should retrieve the correct sales amount based on the selected conditions. It should also handle missing combinations clearly and demonstrate how multi-criteria formulas can simplify reporting and data retrieval.

20. Interview Questions and Answers

Q1. What is a multi-criteria lookup in Excel?

Answer: It is a lookup technique that retrieves data based on two or more conditions, such as employee name, department, and location.

Q2. Why is a single-criteria lookup sometimes insufficient?

Answer: Multiple records may share the same lookup value. Additional criteria help identify the intended record.

Q3. How can INDEX and MATCH handle multiple criteria?

Answer: MATCH can search for 1 in a combined array of logical conditions, while INDEX returns the corresponding value from the result range.

Q4. What does the multiplication operator do in a multi-criteria formula?

Answer: It combines logical tests using AND logic. Only rows satisfying all conditions produce a result of 1.

Q5. What is the purpose of IFERROR?

Answer: IFERROR displays an alternative result when a formula returns an error, such as when no matching record is found.

Q6. Can a multi-criteria lookup use three or more conditions?

Answer: Yes. Additional logical conditions can be included as long as the criteria ranges align correctly.

Q7. What is the difference between AND and OR logic?

Answer: AND requires every condition to be true, while OR requires at least one condition to be true.

Q8. When should FILTER be used instead of INDEX and MATCH?

Answer: FILTER is useful when you need to return all records satisfying the criteria rather than just the first matching record.

Q9. What causes #N/A in a multi-criteria formula?

Answer: No record satisfies all the supplied conditions, or the criteria contain spelling, formatting, or range-reference issues.

Q10. Why are helper columns useful?

Answer: Helper columns can combine multiple fields into a lookup key, making certain formulas easier to understand and maintain.

21. Quick Revision Checklist

• Multi-criteria lookups search using more than one condition.
• INDEX returns the requested result from a range.
• MATCH identifies the relative position of the matching row.
• Multiplying logical conditions applies AND logic.
• Use exact matching for identifiers and text-based criteria.
• Use cell references to make formulas dynamic.
• Ensure all criteria ranges have matching dimensions.
• Use IFERROR to display a helpful message for errors.
• Use XLOOKUP for supported modern Excel workflows.
• Use FILTER when multiple matching records are needed.

22. Conclusion

Building multi-criteria lookup formulas is an essential Advanced Excel skill for retrieving accurate information from complex datasets. By combining INDEX and MATCH with logical conditions, you can identify records based on multiple requirements and reduce the need for manual searching.

Dynamic cell references, error handling, XLOOKUP, concatenated lookup keys, and FILTER provide additional options for designing flexible and maintainable workbooks. Practising these techniques with employee, product, sales, and customer datasets will help you develop stronger spreadsheet analysis skills.

Learn More with JustAcademy

Explore the Advanced Excel Course to develop your formula-writing, lookup, reporting, and data analysis skills.

Register for a Course Demo to learn more about the training program.

Visit the JustAcademy website to explore additional courses and learning resources.